﻿-- Function: pl_function.guardar_mconstr(character varying, character varying, character varying, character varying)

-- DROP FUNCTION pl_function.guardar_mconstr(character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION pl_function.guardar_mconstr(p_text character varying, p_cdepcme character varying, p_cvaluni character varying, p_cnumpis character varying)
  RETURNS integer AS
$BODY$
declare
	ids numeric(18, 0) := 0;
	p_cperini character(4);
	p_cperfin character(4);
	p_dpredio character varying(10);
	p_mconstr character(10);
	p_vhostnm character varying(25);
	p_vusernm character varying(25);
	p_canocon numeric;
	p_narecom numeric(18,5);
	p_narecon numeric(18,5);
begin
	select a.idsigma, a.dpredio, a.vhostnm
		, a.vusernm, a.narecom, a.narecon
		, a.canocon::numeric
	into p_mconstr, p_dpredio, p_vhostnm
		, p_vusernm, p_narecom, p_narecon
		, p_canocon
	from pl_table.tmp_mconstr(p_text) a;

	if p_mconstr = '-1' then
		select coalesce(max(idsigma)::numeric(18, 0), 0) into ids from registro.mconstr;
		p_mconstr := lpad((ids + 1)::varchar, 10, '0');
	end if;
	
	with new_values(  idsigma, dpredio, cnitems, cnumpis,
		cmescon, canocon, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm, narecon
	) as (
		select idsigma, dpredio, cnitems, cnumpis,
			cmescon, canocon, cmateri, cconser,
			cmurcol, ctechos, cmpisos, cpueven,
			crevest, cbanios, celectr, narecom,
			nporcom, nestado, vhostnm, vusernm,
			ddatetm, narecon
		from pl_table.tmp_mconstr(p_text)
	), upsert as ( 
	    update registro.mconstr m set
		dpredio=n.dpredio, cnitems=n.cnitems, cnumpis=n.cnumpis,
		cmescon=n.cmescon, canocon=n.canocon, cmateri=n.cmateri,
		cconser=n.cconser, cmurcol=n.cmurcol, ctechos=n.ctechos,
		cmpisos=n.cmpisos, cpueven=n.cpueven, crevest=n.crevest,
		cbanios=n.cbanios, celectr=n.celectr, narecom=n.narecom,
		nporcom=n.nporcom, nestado=n.nestado, vhostnm=n.vhostnm,
		vusernm=n.vusernm, ddatetm=n.ddatetm, narecon=n.narecon
	    from new_values n
	    where m.idsigma = n.idsigma
	    returning m.*
	)
	insert into registro.mconstr(idsigma, dpredio, cnitems, cnumpis,
		cmescon, canocon, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm, narecon
	) select p_mconstr idsigma, dpredio, cnitems, cnumpis,
		cmescon, canocon, cmateri, cconser,
		cmurcol, ctechos, cmpisos, cpueven,
		crevest, cbanios, celectr, narecom,
		nporcom, nestado, vhostnm, vusernm,
		ddatetm, narecon
	from new_values where not exists(select 1 from upsert where upsert.idsigma=new_values.idsigma);

	select c.cperini, rpad(coalesce(trim(c.cperfin), ''), 4, '*') into p_cperini, p_cperfin
	from registro.dpredio b inner join registro.mhresum c on b.mhresum=c.idsigma
	where b.idsigma=p_dpredio;

	if p_cperfin = '****' then
		select max(cperiod) into p_cperfin from public.mperiodo;
	end if;
	
	delete from registro.dconstr where mconstr=p_mconstr;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids from registro.dconstr;
	
	insert into registro.dconstr(
		    idsigma, mconstr, nantigu, nvaluni, nincrem, npordep, ndepred, 
		    nvalare, nvalcom, nvalpis, nestado, vhostnm, vusernm, ddatetm, 
		    cperiod)
	select lpad(((row_number() over(order by ax.cperiod)) + ids)::varchar, 10, '0') idsigma
		, p_mconstr mconstr
		, ax.nantigu
		, ax.nvaluni
		, ax.nvaluni * ax.nincrem nincrem
		, ax.npordep
		, ax.nvaluni * ax.npordep / 100 ndepred
		, ax.nvaluni
		+ (ax.nvaluni * ax.nincrem)
		- (ax.nvaluni * ax.npordep / 100) nvalare
		, 0 nvalcom
		, round((ax.nvaluni
		+ (ax.nvaluni * ax.nincrem)
		- (ax.nvaluni * ax.npordep / 100)
		) * p_narecon, 2) nvalpis
		, 1 nestado
		, p_vhostnm vhostnm
		, p_vusernm vusernm
		, now() ddatetm
		, ax.cperiod
	from (
		select a.cperiod
			, (a.cperiod::int - p_canocon::int) nantigu
			, sum(c.ndecima) nvaluni
			, case when right(p_cnumpis, 2) = 'PI' then (case when left(p_cnumpis, 2)::int >= 5 then 0.05 else 0.0 end) else 0.0 end nincrem
			, b.ndecima npordep
		from public.mperiodo a
			inner join public.mvalcal b on a.cperiod=b.cperiod
				and b.ctipcol='2'
				and b.column0=((case when ((((a.cperiod::int - p_canocon::int) - 1) / 5)::int * 5 + 5) > 55 then 55
					  else ((((a.cperiod::int - p_canocon::int) - 1) / 5)::int * 5 + 5) end)::varchar || p_cdepCME)
			inner join public.mvalcal c on a.cperiod=c.cperiod
				and c.ctipcol='1'
				and c.column0 in(
					substring(p_cvaluni from 1 for 1) || '1',
					substring(p_cvaluni from 2 for 1) || '2',
					substring(p_cvaluni from 3 for 1) || '3',
					substring(p_cvaluni from 4 for 1) || '4',
					substring(p_cvaluni from 5 for 1) || '5',
					substring(p_cvaluni from 6 for 1) || '6',
					substring(p_cvaluni from 7 for 1) || '7'
				)
		where a.cperiod>=p_cperini and a.cperiod<=p_cperfin and a.cperiod::int>=p_canocon::int
		group by a.cperiod
			, (a.cperiod::int - p_canocon::int)
			, case when right(p_cnumpis, 2) = 'PI' then (case when left(p_cnumpis, 2)::int >= 5 then 0.05 else 0.0 end) else 0.0 end
			, b.ndecima
	) ax;

	select pl_function.guardar_vpredio(p_dpredio, p_vhostnm, p_vusernm) into ids;
	return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.guardar_mconstr(character varying, character varying, character varying, character varying)
  OWNER TO desarrollo;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mconstr(character varying, character varying, character varying, character varying) TO desarrollo;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mconstr(character varying, character varying, character varying, character varying) TO public;
